USE HierarchyIdDemo
GO

-- CREATE a PROCEDURE to make it easier to move a department
-- under a different department
CREATE PROCEDURE HierarchyId.MoveDepartment
	@DepartmentID INT, 
	@NewParentID INT
AS
BEGIN

	DECLARE @OldNode hierarchyid,
		@ParentNode hierarchyid,
		@MaxChildNode hierarchyid,
		@NewNode hierarchyid

	BEGIN TRANSACTION

		-- need old node to "reparent" descendents
		-- of moved node
		SELECT @OldNode = Node
			FROM HierarchyId.Org
			WHERE DepartmentID = @DepartmentID

		-- get the node for the new parent
		SELECT @ParentNode = Node 
			FROM HierarchyId.Org 
			WHERE DepartmentID = @NewParentID

		-- get the last child node for the new parent
		SELECT @MaxChildNode = max(Node) 
			FROM HierarchyId.Org 
			WHERE @ParentNode = Node.GetAncestor(1)

		-- get the next descendent for the new parent
		SELECT @NewNode = @ParentNode.GetDescendant(@MaxChildNode, NULL)

		-- move the node and all descendants using the
		-- reparent method.  Since a node is it's own
		-- descendant, this also reassigns the node itself
		UPDATE HierarchyId.Org
				SET Node = Node.Reparent(@OldNode, @NewNode)
			WHERE @OldNode.IsDescendant(Node) = 1

	COMMIT

END

GO

EXEC HierarchyId.ShowDepartmentChart 1

-- move parking to part of
-- home operations
EXEC HierarchyId.MoveDepartment 4, 5

EXEC HierarchyId.ShowDepartmentChart 1